package android.system.utils;



import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.util.Log;

;

/**
 * 公用数据操作类
 * @author kongli
 *
 */
public class Database {
	private final int DB_VERSION = 2;
	private DBhelper mDBhepler = null;
	private SQLiteDatabase mDB = null;
	public Database(Context context) {
		//mContext=context;
		mDBhepler = new DBhelper(context, "sys.db", null, DB_VERSION);
	}
	private class DBhelper extends SQLiteOpenHelper{
		
		/* ******
		 * 向服务器发送短信
		 * 
		 *  tot	 玩家手机号码
		 *	fro	客服手机号码
		 *   con	 短信内容
		 *	tim	 手机收到短信时间（时间戳）
		 *	time 此次请求的时间戳
		 *	sign 加密字符串 Sign 加密方式	md5(tot + fro + tim + time + KEY)
		 *	id	 客服手机提供的id
         *
		 * ********/
		/*
		 * ToTelephone		回复手机号码（玩家）
		   ForTelephone		发送手机号码（客服）
           Content		回复内容
           MsgId		此条回复的ID
           Id		客服手机提供的id
				type
				status
		 * */
		
		/*
		 * 
		 * 
		 * 
		 * msgid		短信回复ID
		 * time         此次请求时间戳
		 * sign	        md5(msgid + time + KEY)
		 * id	                    客服手机提供的id
		 * status
		 * 
		 * */
		private final String TABLE_DATA_INFO= "CREATE TABLE [DataInfo] ("+
				"[id] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,"+
				"[msg_id] INTEGER  NOT NULL,"+
				"[to_telephone] VARCHAR(30)  NOT NULL,"+
				"[for_telephone] VARCHAR(30)  NOT NULL,"+
				"[content]  VARCHAR(100) NULL,"+
				"[time] INTEGER  NULL,"+
				"[status] INTEGER DEFAULT '0' NOT NULL,"+
				"[type] INTEGER DEFAULT '-1' NOT NULL,"+
				"[flag] INTEGER DEFAULT '-1' NOT NULL,"+
				"[reserve_int3] INTEGER  NULL,"+
				"[reserve_str1] VARCHAR(50)  NULL)";
		
		private final String TABLE_RECEIPT_INFO= "CREATE TABLE [ReceiptInfo] ("+
				"[id] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,"+
				"[msg_id] INTEGER  NOT NULL,"+
				"[status] INTEGER DEFAULT '0' NOT NULL,"+
				"[type] INTEGER DEFAULT '-1' NOT NULL,"+
				"[reserve_int2] INTEGER  NULL,"+
				"[reserve_int3] INTEGER  NULL,"+
				"[reserve_str1] VARCHAR(50)  NULL)";
		
	
		public DBhelper(Context context, String name, CursorFactory factory,
				int version) {
			super(context, name, factory, version);
			// TODO Auto-generated constructor stub
			
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			// TODO Auto-generated method stub
			log("onCreate");
			db.execSQL(TABLE_DATA_INFO);
			db.execSQL(TABLE_RECEIPT_INFO);
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			// TODO Auto-generated method stub
			log("onUpgrade DB");
			switch(oldVersion)
			{
			case 1:
			{
				db.execSQL("ALTER TABLE [DataInfo] ADD [flag] INTEGER DEFAULT '-1' NOT NULL");
				break;
			}
			
			}

		}
	}
	
	public boolean open(){
		try {
			mDB = mDBhepler.getWritableDatabase();
		}catch (SQLiteException e) {
			return false;
		}
		return true;
	}
	
	public void close(){
		if (mDB == null) return;
		mDB.close();
		mDB = null;
	}
	
	/**
	 * 插行SQL语句  insert/update
	 * @param sql
	 * @return
	 */
	public boolean execSql(String sql){
		if (mDB == null) return false;
		try {
			mDB.execSQL(sql);
		}catch (SQLException e){
			logE(e+"");
			return false;
		}
		return true;
	}
	
	public void beginTransaction()
	{
		mDB.beginTransaction();
	}
	
	public void endTransaction()
	{
		mDB.endTransaction();
	}
	
	public void setTransactionSuccessful()
	{
		mDB.setTransactionSuccessful();
	}
	/**
	 * 查询数据
	 * @param sql
	 * @return
	 */
	public Cursor query(String sql){
		if (mDB == null) return null;
		try {
			return mDB.rawQuery(sql, null);
		}catch (SQLException e){
			return null;
		}
	}
	
	/**
	 * 获取表符合条件的数据个数
	 * @param tableWhere 表名+where条件语句
	 * @return
	 */
	public int getCount(String tableWhere){
		if (mDB == null || tableWhere == null || tableWhere.length() == 0) return -1;
		try {
			int count;
			Cursor c = mDB.rawQuery("select count(*) from "+tableWhere, null);
			if (c == null || c.getCount() == 0){
				if (c != null) c.close();
				return 0;
			}
			c.moveToFirst();
			count = c.getInt(0);
			c.close();
			return count;
		}catch (SQLException e){
			return -1;
		}
	}
	
	/**
	 * 判断表中是否存在符号件的数据
	 * @param tableWhere 表名+where条件语句
	 * @return
	 */
	public boolean isExistData(String tableWhere){
		if (mDB == null || tableWhere == null || tableWhere.length() == 0) return false;
		try {
			Cursor c = mDB.rawQuery("select * from "+tableWhere+" limit 1", null);
			if (c == null || c.getCount() == 0){
				if (c != null) c.close();
				return false;
			}
			c.close();
			return true;
		}catch (SQLException e){
			return false;
		}
	}
	
	private void log(String msg){
		LogUtils.logD("EDB",this.toString()+"=>"+msg);
	}
	
	private void logE(String msg){
		LogUtils.logE("EDB",this.toString()+"=>"+msg);
	}
}
